package com.demo.wx;

import java.io.IOException;
import java.sql.SQLException;
import java.util.List;

import util.DateUtils;
import me.chanjar.weixin.common.exception.WxErrorException;
import me.chanjar.weixin.mp.api.WxMpInMemoryConfigStorage;
import me.chanjar.weixin.mp.api.WxMpMessageRouter;
import me.chanjar.weixin.mp.api.WxMpService;
import me.chanjar.weixin.mp.api.WxMpUserService;
import me.chanjar.weixin.mp.api.impl.WxMpServiceImpl;
import me.chanjar.weixin.mp.api.impl.WxMpUserServiceImpl;
import me.chanjar.weixin.mp.bean.message.WxMpXmlMessage;
import me.chanjar.weixin.mp.bean.message.WxMpXmlOutTextMessage;
import me.chanjar.weixin.mp.bean.result.WxMpUser;

import com.demo.common.model.Loginfo;
import com.demo.common.model.MoneyItem;
import com.demo.common.model.Userinfo;
import com.demo.common.model.base.BaseUserinfo;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.ActiveRecordException;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class FamilyXiaoZhangController extends Controller{
	
	
	
	protected static WxMpInMemoryConfigStorage config;
	protected static WxMpService wxMpService;
	protected static WxMpMessageRouter wxMpMessageRouter;
	protected static String token="familyWx";
	
	static{
		config = new WxMpInMemoryConfigStorage();
		config.setToken(token);    // 设置微信公众号的token
		config.setAppId("wx394479cf5837157b");    // 设置微信公众号的appid
		config.setSecret("9ba1d0d5ee3ef5b2249d67641f7fbaad");  // 设置微信公众号的app corpSecret
	    wxMpService = new WxMpServiceImpl();
		wxMpService.setWxMpConfigStorage(config);
	}
	
	/**
	 * 微信服务器验证
	 * @return
	 */
	public void familyWx(){
		MoneyItem moneyItem = null;
		Userinfo userinfo = null;
		String method = getRequest().getMethod();
		if("GET".equals(method)){
			System.out.println("getqingqiu");
			// 微信加密签名  
	        String signature = getRequest().getParameter("signature");  
	        // 时间戳  
	        String timestamp = getRequest().getParameter("timestamp");  
	        // 随机数  
	        String nonce = getRequest().getParameter("nonce");  
	        // 随机字符串  
	        String echostr = getRequest().getParameter("echostr");  
	        //校验参数
	       if(wxMpService.checkSignature(timestamp, nonce, signature)){
	    	 renderText(echostr);
	        }else{
	          renderText("");
	        }
		}else if("POST".equals(method)){
			System.out.println("post请求");
			WxMpXmlMessage message;
			try {
				message = WxMpXmlMessage.fromXml(getRequest().getInputStream());
				System.out.println(message);
				String openid = message.getFromUser();
				
				WxMpUserService mpUserService = new WxMpUserServiceImpl(wxMpService);
				WxMpUser wxMpUser = null;
				String username = "";
				try {
					wxMpUser = mpUserService.userInfo(openid);
					username = wxMpUser.getNickname();
				} catch (WxErrorException e) {
					e.printStackTrace();
				}
				
				String result = "";	
				System.out.println("------------message.getMsgType()=="+message.getMsgType());
				if(message.getMsgType()!=null  && "text".equals(message.getMsgType())){
			         if(message.getContent()!=null && !"".equals(message.getContent())){
			            String content=	 message.getContent();
			            
			            System.out.println(username+"发送命令："+content);
			            try{
			            	Loginfo loginfo = new Loginfo();
				            loginfo.setOpenid(openid);
				            if(username.indexOf("\\x")<0){
				            	loginfo.setUsername(username);
				            }else {
				            	loginfo.setUsername("");
							}
				            loginfo.setMsg(username+"发送命令："+content);
				            loginfo.setAction("text");
				            loginfo.save();//保存日志
			            }catch(ActiveRecordException e){
			            	username = "emoji表情,无法识别";
			            	Loginfo loginfo = new Loginfo();
				            loginfo.setOpenid(openid);
				            loginfo.setUsername(username);
				            loginfo.setMsg(username+"发送命令："+content);
				            loginfo.setAction("text");
				            loginfo.save();//保存日志
			            }
			            
			            
			            List<Userinfo> list = Userinfo.dao.find("select * from userinfo where openid='"+openid+"'");
						if(list == null || list.size()==0){//不管发送什么命令，openid不存在，先保存openid
							userinfo = new Userinfo();
							userinfo.setOpenid(openid);
							userinfo.setUsername(username);
							userinfo.save();
							list = Userinfo.dao.find("select * from userinfo where openid='"+openid+"'");
						}
			            
			            if(content.contains("@")){
			            	String[] strSplit = content.split("@");
			            	if(strSplit.length!=2){
			            		result = "格式不正确\n正确格式例如：5.2@早餐";
			            	}else {
								double money = 0.00;
								try{
									money = Double.valueOf(strSplit[0]);
								}catch(Exception e){
									e.printStackTrace();
									result = "格式不正确\n正确格式例如：5.2@早餐";
								}
								
								moneyItem = new MoneyItem();
								moneyItem.setOpenid(openid);
								moneyItem.setMoney(money);
								moneyItem.setBeizhu(strSplit[1]);
								moneyItem.setNowyear(DateUtils.getNowYear());
								moneyItem.setNowmonth(DateUtils.getNowMonth());
								moneyItem.setNowday(DateUtils.getNowDay());
								moneyItem.setNowtime(DateUtils.getNowTime());
								moneyItem.save();
								
//								String sqlMonth = "select SUM(money) as moneyMonth from moneyItem where openid='"+openid+"' and  nowmonth= '"+DateUtils.getNowMonth()+"' GROUP BY openid";
//								Record recordMonth = Db.findFirst(sqlMonth);
//								double resultMonth = 0.00;
//								if(recordMonth != null && recordMonth.getDouble("moneyMonth") != null){
//									resultMonth = recordMonth.getDouble("moneyMonth");
//								}
								double resultMonth = getMonthTotal(openid, DateUtils.getNowMonth(),DateUtils.getNowYear());
								
								
//								String sqlDay = "select SUM(money) as moneyDay from moneyItem where openid='"+openid+"' "
//										+ "and  nowmonth= '"+DateUtils.getNowMonth()+"' and nowday = '"+DateUtils.getNowDay()+"' GROUP BY openid";
//								Record recordDay = Db.findFirst(sqlDay);
//								double resultDay = 0.00;
//								if(recordDay != null && recordDay.getDouble("moneyDay") != null){
//									resultDay = recordDay.getDouble("moneyDay");
//								}
								
								double resultDay = getDayTotal(openid, DateUtils.getNowMonth(),DateUtils.getNowDay(),DateUtils.getNowYear());
								
								
								result = "记录成功\n当日已消费："+resultDay+"元，当月已消费："+resultMonth+"元！";
								result += "\n";
								int groupid = 0;
								if(list != null && list.size()>0){
									userinfo = list.get(0);
									if(userinfo.getGroupid() != null){
										groupid = userinfo.getGroupid();
										result += getFamilyTotal(groupid, DateUtils.getNowMonth(), DateUtils.getNowDay(),DateUtils.getNowYear());
									}
								}
							}
						}else if (content.contains("年消费")&&!"当年消费".equals(content)) {
							String str = content.replace("年消费", "");
							int aa = 0;
							try{
								aa = Integer.parseInt(str);
							}catch(Exception e){
								result = "输入格式不正确\n如查询某月消费请回复：8月消费,则可查询当年8月消费总数";
							}
							if(aa>=2017){
								double resultMonth = getYearTotal(openid, aa+"");
								result = aa+"年已消费金额："+resultMonth+"元";
								if(aa == 2017){
									result += "\n注：2017年数据从8月12日开始计算";
								}
							}else {
								result = "输入格式不正确\n如查询某年消费请回复：2017年消费,则可查询2017年消费总数,仅可查询2017年及以后的数据。";
							}
						}else if (content.contains("月消费")&&!"当月消费".equals(content)) {
							String str = content.replace("月消费", "");
							int aa = 0;
							try{
								aa = Integer.parseInt(str);
							}catch(Exception e){
								result = "输入格式不正确\n如查询某月消费请回复：8月消费,则可查询当年8月消费总数";
							}
							if(aa>0 && aa<=12){
								double resultMonth = getMonthTotal(openid, aa+"",DateUtils.getNowYear());
								result = DateUtils.getNowMonth()+"月"+aa+"日已消费金额："+resultMonth+"元";
							}else {
								result = "输入格式不正确\n如查询某月消费请回复：8月消费,则可查询当年8月消费总数,月数须大于0且小于等于12";
							}
						}else if (content.contains("日消费")&&!"当日消费".equals(content)) {
							String str = content.replace("日消费", "");
							int aa = 0;
							try{
								aa = Integer.parseInt(str);
							}catch(Exception e){
								result = "输入格式不正确\n如查询当月某日消费请回复：15日消费,则可查询本月15日消费总数";
							}
							if(aa>0 && aa<=31){
								double resultDay = getDayTotal(openid, DateUtils.getNowMonth(),aa+"",DateUtils.getNowYear());
								result = DateUtils.getNowMonth()+"月"+aa+"日已消费金额："+resultDay+"元";
							}else {
								result = "输入格式不正确\n如查询当月某日消费请回复：15日消费,则可查询本月15日消费总数,日期数须大于0且小于等于31";
							}
						}else if ("当日消费".equals(content)) {
							double resultDay = getDayTotal(openid, DateUtils.getNowMonth(),DateUtils.getNowDay(),DateUtils.getNowYear());
							result = "当日已消费金额："+resultDay+"元";
						}else if ("当月消费".equals(content)) {
							double resultMonth = getMonthTotal(openid, DateUtils.getNowMonth(),DateUtils.getNowYear());
							result = "当月已消费金额："+resultMonth+"元";
						}else if ("当年消费".equals(content)) {
							double resultMonth = getYearTotal(openid, DateUtils.getNowYear());
							result = "当年已消费金额："+resultMonth+"元";
						}else if (content.contains("明细")) {
							result = "此功能开发中ing...敬请期待...";
						}else if ("家庭消费".equals(content)) {
							result = "该账号不存在家庭分组,如需多人消费同时计算请联系作者！";
							int groupid = 0;
							if(list != null && list.size()>0){
								userinfo = list.get(0);
								if(userinfo.getGroupid() != null){
									groupid = userinfo.getGroupid();
									result = getFamilyTotal(groupid, DateUtils.getNowMonth(), DateUtils.getNowDay(),DateUtils.getNowYear());
								}
							}
						}else if ("帮助".equals(content)) {
							result = username+"您好，欢迎关注！\n请回复：消费金额@消费备注"
									+ "\n就会记录."
									+ "\n同时会回复:"
									+ "\n当日消费总数和当月消费总数"
									+ "\n如果有记录错误"
									+ "\n清回复相应负数冲账"
									+ "\n回复:当日消费,则可查询当日消费总数"
									+ "\n回复:当月消费,则可查询当月消费总数"
									+ "\n回复:当年消费,则可查询当年消费总数"
									+ "\n回复:2017年消费,则可查询2017年消费总数"
									+ "\n回复:8月消费,则可查询当年8月消费总数"
									+ "\n回复:15日消费,则可查询当月15日消费总数"
									+ "\n回复:2017.08.13明细,则可查询指定日期消费明细"
									+ "\n回复:家庭消费可以查询家庭消费总数,仅限存在家庭分组用户使用"
									+ "\n如需要按多人为单位记录消费,请联系作者！本功能暂未开发自行修改"
									+ "\n消费明细稍后开发，敬请期待！";
						}else {
							result = "系统未识别您输入的文字\n请回复 帮助 查询正确用法！感谢使用！";
						}
			         }
				}
				if(message.getEvent()!=null && "subscribe".equals(message.getEvent())){
					result = username+"您好，欢迎关注！"
							+ "\n请回复：消费金额@消费备注"
							+ "\n就会记录.\n同时会回复:"
							+ "\n当日消费总数和当月消费总数"
							+ "\n如果有记录错误"
							+ "\n清回复相应负数冲账"
							+ "\n回复:当日消费,则可查询当日消费总数"
							+ "\n回复:当月消费,则可查询当月消费总数"
							+ "\n回复:当年消费,则可查询当年消费总数"
							+ "\n回复:2017年消费,则可查询2017年消费总数"
							+ "\n回复:8月消费,则可查询当年8月消费总数"
							+ "\n回复:15日消费,则可查询当月15日消费总数"
							+ "\n回复:2017.08.13明细,则可查询指定日期消费明细"
							+ "\n回复:家庭消费可以查询家庭消费总数,仅限存在家庭分组用户使用"
							+ "\n如需要按多人为单位记录消费,请联系作者！本功能暂未开发自行修改"
							+ "\n消费明细稍后开发，敬请期待！";
					Loginfo loginfo = new Loginfo();
		            loginfo.setOpenid(openid);
		            loginfo.setUsername(username);
		            loginfo.setMsg(username+"关注");
		            loginfo.setAction("subscribe");
		            loginfo.save();//保存日志
				}
				
				WxMpXmlOutTextMessage text=WxMpXmlOutTextMessage.TEXT().toUser(openid).fromUser(message.getToUser()).content(result+"\n回复帮助可查看全部功能!").build();	
			    String xml=text.toXml();
				renderText(xml);
				
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
	}
	/**
	 * 获取指定年消费总数
	 * @return
	 */
	public double getYearTotal(String openid,String year) {
		String sqlMonth = "select SUM(money) as moneyMonth from moneyItem where openid='"+openid+"' and  nowyear= '"+year+"' GROUP BY openid";
		Record recordMonth = Db.findFirst(sqlMonth);
		double resultMonth = 0.00;
		if(recordMonth != null && recordMonth.getDouble("moneyMonth") != null){
			resultMonth = recordMonth.getDouble("moneyMonth");
		}
		return resultMonth;
	}
	/**
	 * 获取指定月消费总数
	 * @return
	 */
	public double getMonthTotal(String openid,String month,String year) {
		String sqlMonth = "select SUM(money) as moneyMonth from moneyItem where openid='"+openid+"' and nowyear = '"+year+"' and nowmonth= '"+month+"' GROUP BY openid";
		Record recordMonth = Db.findFirst(sqlMonth);
		double resultMonth = 0.00;
		if(recordMonth != null && recordMonth.getDouble("moneyMonth") != null){
			resultMonth = recordMonth.getDouble("moneyMonth");
		}
		return resultMonth;
	}
	/**
	 * 获取指定日消费总数
	 * @return
	 */
	public double getDayTotal(String openid,String month,String day,String year) {
		String sqlDay = "select SUM(money) as moneyDay from moneyItem where openid='"+openid+"' "
				+ "and nowyear = '"+year+"' and  nowmonth= '"+month+"' and nowday = '"+day+"' GROUP BY openid";
		Record recordDay = Db.findFirst(sqlDay);
		double resultDay = 0.00;
		if(recordDay != null && recordDay.getDouble("moneyDay") != null){
			resultDay = recordDay.getDouble("moneyDay");
		}
		return resultDay;
	}
	/**
	 * 获取指定日消费明细
	 * @return
	 */
	public String getDayItem(String day) {
		
		return null;
	}
	/**
	 * 获取指定日消费明细
	 * @return
	 */
	public String getFamilyTotal(int groupid,String month,String day,String year) {
		String sql = "select openid from userinfo where groupid="+groupid;
		List<Userinfo> list = Userinfo.dao.find(sql);
		String result = "";
		String str = "";
		if(list.size()>=2){
			for(int i=0;i<list.size();i++){
				Userinfo userinfo2 = list.get(i);
				str += "'"+userinfo2.getOpenid()+"',";
			}
			str = str.substring(0,str.length()-1);
			//select SUM(a.money) from moneyItem a , userinfo b where a.openid = b.openid and b.groupid=1314 GROUP by groupid
			String sqlMonth = "select SUM(money) as moneyMonth from moneyItem a , userinfo b "
					+ "where a.openid = b.openid and a.nowyear = '"+year+"' and a.nowmonth= '"+month+"' and b.groupid = "+groupid+" GROUP BY groupid";
			System.out.println("sqlMonth=="+sqlMonth);
			Record recordMonth = Db.findFirst(sqlMonth);
			double resultMonth = 0.00;
			if(recordMonth != null && recordMonth.getDouble("moneyMonth") != null){
				resultMonth = recordMonth.getDouble("moneyMonth");
			}
			
			String sqlDay =  "select SUM(money) as moneyDay from moneyItem a , userinfo b "
					+ "where a.openid = b.openid and a.nowyear = '"+year+"' and  a.nowmonth= '"+month+"' and b.groupid = "+groupid+" and nowday = '"+day+"' GROUP BY groupid";
			Record recordDay = Db.findFirst(sqlDay);
			System.out.println("sqlDay=="+sqlDay);
			double resultDay = 0.00;
			if(recordDay != null && recordDay.getDouble("moneyDay") != null){
				resultDay = recordDay.getDouble("moneyDay");
			}
			
			String sqlYear =  "select SUM(money) as moneyYear from moneyItem a , userinfo b "
					+ "where a.openid = b.openid and   b.groupid = "+groupid+" and a.nowyear = '"+year+"' GROUP BY groupid";
			Record recordYear = Db.findFirst(sqlYear);
			System.out.println("sqlYear=="+sqlYear);
			double resultYear = 0.00;
			if(recordYear != null && recordYear.getDouble("moneyYear") != null){
				resultYear = recordYear.getDouble("moneyYear");
			}
			
			result += "家庭当日已消费："+resultDay+"元，家庭当月已消费："+resultMonth+"元，家庭当年已消费："+resultYear+"元！";
		}
		return result;
	}
	

}
